Dynamics GP to Business Central Migration Plan

Current Integrations

Donations

Donations are integrated with GP by virtue of the fact that bundles and batches are pushed to GP as general ledger entries, and changes are prevented for donations that belong to batches that have already been pushed to GP.

• Bundles\BundleList.xaml.cs
  • BundleList_OnLoaded retrieves a list of bundles from Olivet via #GetBundleListAsync|ICommunicator.GetBundleListAsync.
  • Post_Click pushes a bundle to GP via #PostBundleToGPAsync|ICommunicator.PostBundleToGPAsync
• Bundles\BundleList.xaml
  • Post To GP Button visibility is bound to the value of IsPostedToGP
• BulkDonationEntry\Manual\ViewModels\BulkDonationEntryViewModel.cs
  • PostToGP pushes a bundle to GP via #PostBundleToGPAsync|ICommunicator.PostBundleToGPAsync
  • Defines a property IsBundlePostedToGP - not sure why this exists when BundleSummary also has the same property. More investigation will be necessary.
• BulkDonationEntry\Manual\Views\BulkDonationEntry.xaml
  • RadButton command bound to PostToGPCommand in BulkDonationEntry\Manual\ViewModels\BulkDonationEntryViewModel.cs

Halls (ChurchInfo)

• HallInfoViewModel.cs
  • RetrieveInfoAsync gets a vendor ID, contract expiry and 1099 exempt status via #ChurchInfoRetrieveRecordAsync|ICommunicator.ChurchInfoRetrieveRecordAsync
  • DeleteHall DELETES a hall via #DeleteHall|ICommunicator.DeleteHallAsync
  • SaveHall UPSERTS a hall via #CreateUpdateHallInfoAsync|ICommunicator.CreateUpdateHallInfoAsync
• HallSearch.xaml.cs
  • NewHallButton_Click and HallResults_DoubleClick populate _hcoResults via #ChurchInfoComboFieldsAsync|ICommunicator.ChurchInfoComboFieldsAsync, which is passed into any hall windows to populate combo boxes with valid options.
• Stored Procedure #STP_ExpiredContracts|STP_ExpiredContracts is executed nightly via SQL Server Agent ContractExpiry.

ICommunicator

AddAMember

Old Communicator	New Communicator
Passes a Vendor ID into STP_WEBCH_MEMBERS_Add, which in turn checks to ensure that vendor ID doesn't already exist. Assuming that's the case, it updates GP_VENDOR_ID to match the supplied value.	Appears to somehow not be currently implemented on the REST API, even though the NewCommunicator is trying to talk to it. The backend stored procedure is sec.RIS_create_member, which performs the same checks as STP_WEBCH_Members_Add.

No updating of GP occurs directly in these procedures; that is handled via #TRG_MEMBERS_UPDATE_GP|TRG_MEMBERS_UPDATE_GP

ChurchInfoComboFieldsAsync

Old Communicator	New Commuicator
Calls [#wc.CI_GetComboOptions	wc.CI_GetComboOptions](#wc.CI_GetComboOptions

Status Codes	Events	Contract Types	1099Status	Class
A	Annual Convention	[empty string]	[empty sting]	HALLS
I	Annual Meeting	None	No	Z-CC
	Annual Services	Verbal - Not on File	Yes-Charity
	Bi-Weekly Service	Verbal - On file	Yes-Corporation
	Bible Study	Written - Not on File	Yes-CreditCard
	LYP	Written - On File	Yes-Government
	Special Event		Yes-Under600prYrNoW9
	TWSP

ChurchInfoRetrieveRecordAsync

Old Communicator	New Communicator
For direct SQL connections, calls are made to wc.CI_GetHall and wc.CI_GetHallHistory. For SOAP connections, a call to Soap.ChurchInfoRetrieveRecordAsync is made. SOAP implementation will be removed and not recreated due to a) EOL of SOAP, and b) no one is using it.	REST API implements a call to sec.CI_Get_Hall which returns both the hall information and the history of that table in the one call.

The HallInfoRecord type returned contains VendorID (string) and ContractExpire (DateTime?)

CreateUpdateHallInfoAsync

Old Communicator	New Commuicator
Calls wc.CI_UpdateCreateHall for direct, and a SOAP implementation for SOAP. SOAP is EOL so will not be implemented, as no one is using it.	Calls sec.CI_create_update_hall

INSERTing OR UPDATEing causes the #TRG_CHURCH_INFO_GP_UPDATE|TRG_CHURCH_INFO_GP_UPDATE trigger to fire, which in turn pushes that vendor to GP.

DeleteHall

Old Communicator	New Commuicator
Calls wc.CI_DeleteHall for direct SQL connections. SOAP version is not implemented, and returns a Result.Fail noting this.	Calls sec.CI_delete_hall.

Only halls that don't have Vendor IDs can be deleted. There are no SQL triggers related to a hall being deleted.

GetBundleListAsync

[!DANGER] Missing This is not yet documented

GetVendorIdFromPin

Old Communicator	New Communicator
Calls [#FCN_GetVendorIDFromPin	FCN_GetVendorIDFromPin](#FCN_GetVendorIDFromPin

PostBundleToGPAsync

[!DANGER] Missing This is not yet documented

UpdateAMember

Old Communicator	New Communicator
Passes a Vendor ID into STP_WEBCH_SetMemberInfoValues, which in turn checks to ensure that vendor ID doesn't already exist. Assuming that's the case, it updates GP_VENDOR_ID to match the supplied value.	Calls sec.RIS_update_member, which performs the same checks as STP_WEBCH_SetMemberInfoValues.

No updating of GP occurs directly in these procedures; that is handled via #TRG_MEMBERS_UPDATE_GP|TRG_MEMBERS_UPDATE_GP

Members

• frmMember.cs integrates with GP:

  • ForceToGP: A button that is visible when:
    1. The user is a finance user
    2. there is currently no vendor ID set.

  This button is bound to call ForceToGP_Click - _vendorId retrieved via GetMemberInfo
  - ForceToGP_Click performs the following actions: 1. Checks to see if the current record is saved. 2. Gets a vendor ID for the specified PIN using #GetVendorIdFromPin|GetVendorIdFromPin 3. Assuming all is good, sets _vendorId to the determined vendor ID so that when the record is saved, it updates. - RetrieveInfo Sets the title of the window to include the vendor ID if one was retrieved via GetMemberInfo

  • Save_Click uses #AddAMember|AddAMember and #UpdateAMember|UpdateAMember respectively to pass the vendor ID we have back to the backend service.

Olivet

wc.CI_GetComboOptions

[!DANGER] Missing This is not yet documented

sec.CI_get_combo_options

[!DANGER] Missing This is not yet documented

FCN_GetVendorIDFromPin

1. Takes the first up to 20 characters of the PIN's last name and uppercases it.
2. Removes anything after a first hyphen, if there is one.
3. Removes foreign accents.
4. Removes any non-alphabetical characters.
5. Trims it to a maximum of eight characters
6. Queries GP Vendor table to find the lowest unused vendor number, and appends it padded with up to 2 zeros.

i.e. "PENMAN" becomes "PENMAN002" etc.

We can avoid round-tripping to Business Central by looking at the vendor IDs in members and tblchurch_info. This would work as long as something never breaks.

sec.FIN_get_vendor_id_from_pin

Mirrors #FCN_GetVendorIDFromPin|FCN_GetVendorIDFromPin.

STP_ExpiredContracts

Finds all records from the tblChurchInfo table with a ci_contract_expiry which is in the past, and which currently has a status of "A". tblChurchInfo is updated so the status for any vendors which are expired should be "I". It also calls GPLCG.dbo.AA_taUpdateCreateVendorRcd to set the hold status for the vendor to "1". Finally, an email is generated and sent to accounting@lcg.org and jewers@lcg.org informing them that the hall has been set to inactive in the halls database and in GP.

-- This procedure runs on a nightly basis just after midnight and disables and vendor contracts that
-- are expired.

-- pull the IDs of expired churches into a cursor.
DECLARE expiredContracts CURSOR FOR	
    SELECT ci_id, 
            ci_vendor_no,
            '<b>' + ISNULL(ci_name3,'[unavailable, ci_id = '+ CAST(ci_id AS VARCHAR(5)) +']') + '</b>''s contract has expired and been marked as inactive in the hall database and GP.
            <br>
            <br>Vendor ID: <b>' + ci_vendor_no + '</b>
            <br>Expiry Date: <b>' + CAST(CAST(ci_contract_expire AS SMALLDATETIME) AS VARCHAR(30)) + '</b>
            <br>Service Type: <b>' + ISNULL(ci_purpose,'[unknown purpose]') + '</b>
            <br>
            <br>This email has been generated automatically, please do not reply to it.'
        FROM tblChurch_Info
        WHERE ci_contract_expire < CAST(GETDATE() AS DATE)
            AND ci_status = 'A'
            AND ci_vendor_no IS NOT NULL
DECLARE @contractId INT, @vendorId VARCHAR(13), @emailText VARCHAR(8000)

OPEN expiredContracts
FETCH NEXT FROM expiredContracts into @contractId, @vendorId, @emailText

WHILE @@FETCH_STATUS = 0
BEGIN

    -- Mark the hall status as inactive.
    UPDATE tblChurch_Info
        SET ci_status = 'I'
        WHERE ci_status = 'A'
            AND ci_id = @contractId
    
    -- update GP to have the vendor on hold.
    INSERT INTO GPLCG.dbo.AA_taUpdateCreateVendorRcd (vendorId, hold, UpdateIfExists)
        SELECT @vendorId, 1, 1
    
    -- email Bonnie, Samanthia and Michelle.
    EXEC msdb.dbo.sp_send_dbmail @profile_name='LCG', 
                                 @body_format ='HTML', 
                                 @recipients = 'accounting@lcg.org; jewers@lcg.org',
                                 @subject = 'Vendor Contract Expired Alert', 
                                 @body = @emailText
    
    FETCH NEXT FROM expiredContracts into @contractId, @vendorId, @emailText
END

CLOSE expiredContracts
DEALLOCATE expiredContracts

TRG_CHURCH_INFO_GP_UPDATE

Executes on INSERT or UPDATE on tblchurch_info. Performs the following steps:

1. Checks to see if the inserted ci_vendor_id has changed. If it has, it rolls back the transaction
2. Creates a Remit address record in GP (assuming ci_remit_address_1 is not null).

INSERT INTO GPLCG.dbo.AA_taCreateVendorAddress 
			(vendorID, adrsCode, vndCntct, address1, address2, address3, city, state, zipCode, UpdateIfExists, cCode)
SELECT  CAST(ci_vendor_no AS CHAR(15)), 
	'REMIT', 
	CAST(ci_remit_name AS CHAR(60)), 
	CAST(ci_remit_address_1 AS CHAR(60)), 
	CAST(ci_remit_address_2 AS CHAR(60)), 
	' ',
	CAST(ISNULL(ci_remit_city,'') AS CHAR(60)), 
	CAST(ISNULL(ci_remit_st,'') AS CHAR(29)),
	CAST(ISNULL(ci_remit_zip,'') AS CHAR(10)), 
	1, 
	CASE WHEN cntry_code IS NULL then 'US' WHEN cntry_code = '' THEN 'US' ELSE CAST(ISNULL(cntry_code,'') AS CHAR(6)) END
FROM inserted

TRG_MEMBERS_UPDATE_GP

1. Checks to see if we're part of the US office. If we're not, it does nothing.
2. Ensures that only one record had been updated at the insert/update, so that we don't have to worry about cross-joining the results.
3. Checks to see if any portion of the address was changed. If it wasn't, it does nothing.
4. Checks to see (if sec_t or thd_t are set to non-null, or if service contains 'PY'), AND the record doesn't contain a vendor ID. If all are true, generates a vendor ID via #FCN_GetVendorIDFromPin|FCN_GetVendorIDFromPin.
5. If a member of Updates or Finance:
   1. Generate an email to accounting@lcg.org informing them of the updated address.
   2. If there is only one address associated with the vendor in GP (via FCN_GetGPAddressCount), update that address in GP.

POCOs

BundleSummary

namespace WebClient.Types.Finance
{
    public class BundleSummary
    {
        public uint BundleId { get; set; }
        public uint ItemCount { get; set; }
#if NETSTANDARD2_0
        public string EarliestDate {get;set;}
        public string LatestDate {get;set;}
#elif NET8_0_OR_GREATER
        public DateOnly EarliestDate { get; set; }
        public DateOnly LatestDate { get; set; }
#endif
        public decimal Amount { get; set; }
        public bool IsPostedToGP { get; set; }
    }
}

HallInfoRecord

namespace WebClient.Types.ChurchInfo
{
    public class HallInfoRecord
    {

        //todo change this to the generic HistoryItem
        public class HistoryItem
        {
            public string Field { get; set; }
            public string Before { get; set; }
            public string After { get; set; }
            public string Blame { get; set; }
            public DateTime? TimeStamp { get; set; }
        }

        public uint Id { get; set; } = 0;
        public ushort? ChurchNo { get; set; }
        public string VendorId { get; set; }
        public string Status { get; set; }
        public string Purpose { get; set; }
        public string Name { get; set; }
        public string StreetAddress { get; set; }
        public string Address2 { get; set; }
        public string City { get; set; }
        public string StateProv { get; set; }
        public string Zip { get; set; }
        public string Country { get; set; }
        public string AlternativeName { get; set; }
        public string AlternativeAttention { get; set; }
        public string AlternativeStreetAddress { get; set; }
        public string AlternativeAddress2 { get; set; }
        public string AlternativeCity { get; set; }
        public string AlternativeStateProv { get; set; }
        public string AlternativeZip { get; set; }
        public string AlternativeCountry { get; set; }
        public string Contact { get; set; }
        public string ContactPhone { get; set; }
        public string ContactFax { get; set; }
        public string ContactEmail { get; set; }
        public string Host { get; set; }
        public string HostPhone { get; set; }
        public string HostEmail { get; set; }
        public uint? SquareFeet { get; set; } 
        public string Contract { get; set; }
        public DateTime? ContractExpire { get; set; }
        public string Comments { get; set; }
        public string SSN { get; set; }
        public string EIN { get; set; }
        public string TaxName { get; set; }
        public string TaxStreetAddress { get; set; }
        public string TaxAddress2 { get; set; }
        public string TaxCity { get; set; }
        public string TaxStateProv { get; set; }
        public string TaxZip { get; set; }
        public string Ten99Exempt { get; set; }
        public string RemitName { get; set; }
        public string RemitStreetAddress { get; set; }
        public string RemitAddress2 { get; set; }
        public string RemitCity { get; set; }
        public string RemitStateProv { get; set; }
        public string RemitZip { get; set; }
        public string RemitCountry { get; set; }
        public string AddUser { get; set; }
        public DateTime? EntryTimestamp { get; set; }
        public string RegionalPastor { get; set; }
        public ulong? RegionalPastorPin { get; set; }
        public string AreaPastor { get; set; }
        public ulong? AreaPastorPin { get; set; }
        public string Class { get; set; }
        public List<HistoryItem> History { get; set; } = new List<HistoryItem>();
    }
}

Migration Plan